package com.controller;

import java.io.IOException;
import java.sql.ResultSet;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;

import com.pojo.User;
import com.util.DBUtil;

/**
 * Servlet implementation class ExportExcel
 */
@WebServlet("/exportExcel")
public class ExportExcel extends HttpServlet {

	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String sql = "select * from user";

		ResultSet set = DBUtil.select(sql, new Object[] {});
		List<User> list = DBUtil.ColumnToField(set, User.class);

		if (null != list && list.size() > 0) {

			String fileName = "账户数据.xls";
			resp.setHeader("Content-disposition",
					"attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1")); // 设置文件头编码格式
			resp.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");// 设置类型
			resp.setHeader("Cache-Control", "no-cache");// 设置头
			resp.setDateHeader("Expires", 0);// 设置日期头

			HSSFWorkbook book = new HSSFWorkbook();// 创建excel文档对象

			HSSFSheet sheet = book.createSheet();// 创建工作表
			CellStyle cellStyle = book.createCellStyle();

			cellStyle.setDataFormat(book.createDataFormat().getFormat("yyyy-MM-dd"));// 对日期进行格式化处理
			
			//表头
			Row row1 = sheet.createRow(0);// 创建行
			Cell t1 = row1.createCell(0);// 创建单元格
			t1.setCellValue("id");// 将单元格赋值
			Cell t2 = row1.createCell(1);
			t2.setCellValue("账号");
			Cell t3 = row1.createCell(2);
			t3.setCellValue("密码");

			for (int i = 0; i < list.size(); i++) {
				User user = list.get(i);
				Row row = sheet.createRow(i+1);// 创建行

				Cell cell1 = row.createCell(0);// 创建单元格
				cell1.setCellValue(user.getId());// 将单元格赋值

				Cell cell2 = row.createCell(1);
				cell2.setCellValue(user.getAccount());
				Cell cell3 = row.createCell(2);
				cell3.setCellValue(user.getPassword());

			}
			// 保存文件
			book.write(resp.getOutputStream());
			// 刷新关闭
			resp.getOutputStream().flush();
			resp.getOutputStream().close();
		}

	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}
